package com.guidinggame.db;

import android.R.string;
import android.app.*;
import android.os.*;
import android.util.Log;
import android.view.*;
import android.widget.*;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.*;

import android.content.*;

/**
 * 
 * @author Thomas
 * @version 2014/12/12 1
 * 
 */
public class DBHelper extends SQLiteOpenHelper {

	// 数据库版本
	private static final int VERSION = 1;

	// 数据库名称
	public static final String DBNAME = "GuidingGame.db";

	public static SQLiteDatabase GuidingGame_db;

	/*
	 * 用户表--实体表
	 */
	public static final String USER_TABLE_ENTITY = "Users";

	/*
	 * 用户表键名 userid:当前用户ID password:当前用户密码 nickname:当前用户昵称 sex:当前用户性别
	 * realname:当前用户姓名 grade:当前用户年级 academy:当前用户学院 class:当前用户班号
	 * openId:当前用户终极访问权限号 label:当前用户个性标签 mood:当前用户心情状态 level:当前用户级别（普通或更高级）
	 * age:当前用户年龄 state:当前用户所在国家 province:当前用户所在省份 address:当前用户家庭住址
	 * head_portraitUrl:当前用户头像保存的url ID_Card:当前用户的身份证号 phone:当前用户电话
	 * email:当前用户email musicList:当前用户歌曲列表
	 * 
	 */
	public static final String[] USER_TABLE_KEY = { "userid", "password",
			"nickname", "sex", "realname", "grade", "academy", "class",
			"openId", "level", "age", "state", "province", "address",
			"head_portraitUrl", "ID_Card", "phone", "email", "label", "mood",
			"musicList" };

	// 用户表键对应值
	public static final String[] USER_TABLE_TYPE = { "varchar", "varchar",
			"varchar", "varchar", "varchar", "varchar", "varchar", "varchar",
			"varchar", "varchar", "varchar", "varchar", "varchar", "varchar",
			"varchar", "varchar", "varchar", "text", "text", "text" };

	/*
	 * 一般调用方式：DBHelper dbHelper=new DBHelper(this, DBHelper.DBName,
	 * null,DBHelper.VERSION);
	 */
	public DBHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
		this.GuidingGame_db = getWritableDatabase();
		this.createUserTable();
		
	}

	public DBHelper(Context context) {
		super(context,DBHelper.DBNAME, null, DBHelper.VERSION);
		this.GuidingGame_db = getWritableDatabase();
		Log.v(this.GuidingGame_db.getVersion()+"", "aaa");
		this.createUserTable();
		
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		
	}

	// 打开数据库,Context.MODE_PRIVATE使写入时会重写数据库内容
	public static SQLiteDatabase openDB(Context context, String dbName) {
		SQLiteDatabase db = context.openOrCreateDatabase(dbName,
				Context.MODE_PRIVATE, null);
		return db;
	}

	// 弃表
	public static void dropTable(SQLiteDatabase db, String table) {
		db.beginTransaction();
		try {
			db.execSQL("DROP TABLE IF EXISTS " + table);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	// 不存在则建表
	private static void createTable(SQLiteDatabase db, String table,
			String[] primaryKeys, String[] keys, String[] types) {
		StringBuilder sb = new StringBuilder(String.format(
				"CREATE TABLE IF NOT EXISTS %s (", table));
		int i = 0;
		for (i = 0; i < keys.length; i++) {
			sb.append(String.format("%s %s,", keys[i], types[i]));
		}
		sb.append("PRIMARY KEY (");
		int j = 0;
		for (j = 0; j < primaryKeys.length - 1; j++) {
			sb.append(String.format("%s,", primaryKeys[j]));
		}
		sb.append(String.format("%s))", primaryKeys[j]));
		db.beginTransaction();
		try {
			db.execSQL(sb.toString());
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	// 建用户表
	public static void createUserTable() {
		createTable(GuidingGame_db, USER_TABLE_ENTITY,
				new String[] { USER_TABLE_KEY[0] }, USER_TABLE_KEY,
				USER_TABLE_TYPE);
	}

	/* 
	 * 插入属性的数组值
	 * 
	 * @param db:数据库名称
	 * 
	 * @param table:被插入表名称
	 * 
	 * @param keys[]:待插入的记录
	 * 
	 */
	public void insert(SQLiteDatabase db,ContentValues values, String table) {
		db.insert(table, null, values);
		db.close();
	}

	/* 
	 * 插入属性的数组值
	 * 
	 * @param db:数据库名称
	 * 
	 * @param table:被插入表名称
	 * 
	 * @param keys[]:插入的元组
	 * 
	 */
	public static void insert(SQLiteDatabase db, String table, Object[] keys) {
		StringBuilder sb = new StringBuilder("INSERT INTO " + table
				+ " VALUES (");
		int i = 0;
		for (i = 0; i < keys.length - 1; i++) {
			sb.append("?,");
		}
		sb.append("?)");
		db.beginTransaction();
		try {
			db.execSQL(sb.toString(), keys);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		db.close();
	}

	/*
	 * 更新数据库某个属性值
	 * 
	 * @param db:数据库名称
	 * 
	 * @param table:数据库中待更新的表名
	 * 
	 * @param cv:更新后的行数据项
	 * 
	 * @param whereClause:更新属性名称判断
	 * 
	 * @param whereArgs:待更新项的满足条件数组
	 */
	private static void update(SQLiteDatabase db, String table,
			ContentValues cv, String whereClause, String[] whereArgs) {
		db.beginTransaction();
		try {
			db.update(table, cv, whereClause, whereArgs);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}
	
	// 根据主码进行行更新
	public void updateLine(int id, String table, ContentValues contentValues) {
			GuidingGame_db.update(table, contentValues, "_id=?",
					new String[] { "0" });
		}
	
	/**
	 * 删除记录
	 * 
	 * @param db:数据库名称
	 * 
	 * @param table:数据库中待删除的表名
	 * 
	 * @param cv:删除后的行数据项
	 * 
	 * @param whereClause:删除属性名称判断
	 * 
	 * @param whereArgs:待删除项的满足条件数组
	 */
	public static void delete(SQLiteDatabase db, String table,
				String whereClause, String[] whereArgs) {
			db.beginTransaction();
			try {
				db.delete(table, whereClause, whereArgs);
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}

	/**
	 * 查询并返回记录指针
	 * 
	 * @param userid:用户id
	 * 
	 * @return:返回用户实体
	 */
	public static String queryUserById(String userid) {
		String test = "";
		Cursor c = DBHelper.GuidingGame_db.rawQuery(String.format(
				"SELECT * FROM %s WHERE %s=?", USER_TABLE_ENTITY,
				USER_TABLE_KEY[0]), new String[] { userid });

		// 从数据库读出，写入对象
		if (c != null)
			test = c.getString(0);

		return test;
	}

	public void close() {
		if (GuidingGame_db != null)
			GuidingGame_db.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}
